package jdbc;

import bean.User;
import utils.JDBCUtilsOld;
import org.junit.Test;

import java.sql.*;

/**
 * Date:2022/2/28
 * Author:ybc
 * Description:
 * 1、获取自增的主键
 * 在添加功能中，若id设置了自动递增，此时可以在添加功能中，直接获取自增的主键
 * a>需要在创建预编译对象时，设置允许获取自增的主键
 * connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
 * Statement.No_GENERATED_KEYS：默认配置，表示不允许获取自增的主键
 * Statement.RETURN_GENERATED_KEYS：若需要获取自增的主键，则必须设置
 * b>获取自增的主键
 * ps.getGeneratedKeys()：将自增的主键以ResultSet返回
 * 解析过程：
 * rs.next();   int id = rs.getInt(1);
 * 2、批处理
 * 首先连接地址需要添加参数：rewriteBatchedStatements=true
 * 使用addBatch()将SQL攒起来
 * 使用executeBatch()一次性将所有攒起来的SQL发送到MySQL中执行
 */
public class SpecialFunctionTest {

    @Test
    public void testInsertMore(){
        long start = System.currentTimeMillis();
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8&rewriteBatchedStatements=true",
                    "root",
                    "123456");
            /*String sql = "insert into t_user(username) values(?)";
            for (int i = 1; i <= 10000; i++) {
                ps = connection.prepareStatement(sql);
                ps.setObject(1, "admin"+i);
                ps.executeUpdate();
            }*/
            String sql = "insert into t_user(username) values(?)";
            ps = connection.prepareStatement(sql);
            for (int i = 1; i <= 10000; i++) {
                ps.setObject(1, "admin"+i);
                //批处理，将SQL以及攒起来
                ps.addBatch();
            }
            ps.executeBatch();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsOld.close(rs, ps, connection);
        }
        long end = System.currentTimeMillis();
        //循环添加耗时20283
        //批处理耗时557
        System.out.println("耗时："+(end-start));
    }

    @Test
    public void testGeneratedKeysInSert(){
        User user = new User(null, "小明", "123356", 23, "男", "123@qq.com");
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/wh0106?characterEncoding=utf8",
                    "root",
                    "123456");
            String sql = "insert into t_user values(null,?,?,?,?,?)";
            ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            ps.setObject(1, user.getUsername());
            ps.setObject(2, user.getPassword());
            ps.setObject(3, user.getAge());
            ps.setObject(4, user.getSex());
            ps.setObject(5, user.getEmail());
            int result = ps.executeUpdate();
            System.out.println("结果："+result);
            //获取添加的数据自增的id
            rs = ps.getGeneratedKeys();
            rs.next();
            int id = rs.getInt(1);
            System.out.println("自增的主键："+id);
            connection.rollback();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsOld.close(rs, ps, connection);
        }
    }

}
